//Starts from CreateVar...

clear all
use "[Folder]\Panel_Ind.dta" 

merge m:1 LopNr_PersonNr using "[Folder]\TimeInv_Ind.dta", keepusing(FodelseLandnamn InvAr FodelseLandnamnMor FodelseLandnamnFar )
drop if _merge==2
drop _merge


merge m:1 LopNr_PeOrgNr  year using "[Folder]\Panel_Firm.dta", keepusing(Ftg_Foradlingsvarde Ftg_Eget_kapital Ftg_Anlaggningstillgangar Ftg_Summa_bruttoinv Ftg_Summa_nettoinv)

drop if _merge==2
drop _merge
sort LopNr_PeOrgNr year
by LopNr_PeOrgNr year: egen FirmSize=count(KU1Ink)

merge 1:1 LopNr_PersonNr LopNr_PeOrgNr year using "[Folder]\AppAddLonStat.dta", keepusing(tjomf tjomf_tot arbtim arbtim_tot)

drop if _merge==2
drop _merge 



// Gen. Years of Education 
nsplit Sun2000niva, digits(1 1 1) generate(SunD1 SunD2 SunD3)
gen Educ_Ar = 0
replace Educ_Ar = 3 if SunD1==9
replace Educ_Ar = 6 if SunD1==1
replace Educ_Ar = 9 if SunD1==2
replace Educ_Ar = 10 if SunD1==3 & SunD2==1
replace Educ_Ar = 11 if SunD1==3 & SunD2==2

replace Educ_Ar = 12 if SunD1==3 & SunD2==3
replace Educ_Ar = 13 if SunD1==4 & SunD2==1
replace Educ_Ar = 14 if SunD1==5 & SunD2==2
replace Educ_Ar = 15 if SunD1==5 & SunD2==3
replace Educ_Ar = 16 if SunD1==5 & SunD2==4
replace Educ_Ar = 17 if SunD1==5 & SunD2==5
replace Educ_Ar = 18 if SunD1==6 & SunD2==0
replace Educ_Ar = 18 if SunD1==6 & SunD2==2
replace Educ_Ar = 20 if SunD1==6 & SunD2==4

drop SunD1 SunD2 SunD3


replace KU1Ink = KU1Ink/10  //Individual income given in 100' kr; div by 10 ==> 1000'
rename Ftg_Summa_nettoinv NetInvTot
rename Ftg_Summa_bruttoinv GrosInvTot
rename Ftg_Anlaggningstillgangar Cap

gen Age = year - Fodelsear
gen logKU1 = log(KU1Ink)
gen logCap=log(Cap)
gen logFtg_Foradlingsvarde = log(Ftg_Foradlingsvarde)
gen logNetInvTot=log(NetInvTot)
gen logGrosInvTot=log(GrosInvTot)


gen Exp=Age - Educ_Ar

recode Kon (1=0) //Male
recode Kon (2=1)


//Generate categories
egen u = rowmiss( logFtg_Foradlingsvarde logCap logGrosInvTot)
egen u2= rowmiss( logFtg_Foradlingsvarde logCap )
gen EmpStat=0
replace EmpStat=1 if !missing( LopNr_PeOrgNr) & KU1Ink>20

replace u=99 if u==0
replace u=0 if u<99
replace u=1 if u==99
replace u2=99 if u2==0
replace u2=0 if u2<99
replace u2=1 if u2==99

gen u3=u
recode u (1=0) if FirmSize<6

gen u4=u2
recode u2 (1=0) if FirmSize<6

gen EmpStatPriv=EmpStat
replace EmpStatPriv=0 if SektorKod<20 | (u4==0 & SektorKod>22 & EmpStat==1)

//Indicator for sec. gen. immigrant
gen ForPar=0
replace ForPar=1 if (FodelseLandnamnFar!=107 & !missing(FodelseLandnamnFar) )| ( FodelseLandnamnMor!=107 & !missing(FodelseLandnamnMor) ) 
gen LandKod=FodelseLandnamn


merge m:1 LandKod using "[Folder]\Kontinentkod.dta" //Pull continent data from SCB data base (based on country of birth data).

drop _merge 


//Generate continent code
gen AdjCont=0
replace AdjCont=1 if FodelseLandnamn==107 & ForPar==0
replace AdjCont=2 if Continent==99 & ForPar==1
replace AdjCont=3 if Continent==3 | Continent==4 | Continent==6
replace AdjCont=4 if Continent==2 | Continent==7
replace AdjCont=5 if Continent==1
replace AdjCont=6 if Continent==5
replace AdjCont=7 if Continent==9
gen For=0
replace For =1 if FodelseLandnamn!=107
drop if FodelseLandnamn==87
drop if year==.
drop if FodelseLandnamn==.
drop if AdjCont==0

sort LopNr_PeOrgNr year
by LopNr_PeOrgNr year: egen FirmForShare=mean(For)

//City averages
sort Kommun year
by Kommun year: egen KomFor=mean(For)

//Continent averages
sort AdjCont year
by AdjCont year: egen AvTjomf = mean(tjomf)
by AdjCont year: egen AvArbtim = mean(arbtim)

save "[Folder]\SumStatData.dta"


//Put values in excel table
putexcel set "[Output Folder]\SumStatMaleWorker", replace
putexcel A2 ="Native-Born Parents"
putexcel A3 ="Sec. Gen. Mig."
putexcel A4 ="Europe"
putexcel A5 ="Asia"
putexcel A6 ="Africa"
putexcel A7 ="North Am."
putexcel A8 ="South Am."
putexcel B1 ="Total Nbr of Working-Age"
putexcel C1 ="Share Employed"
putexcel D1 ="Share Private-Sector Employed"
putexcel E1 ="Share in Sample"
putexcel F1 ="Share in Inv. Sample"
putexcel G1 ="Av. Age In Sample"
putexcel H1 ="Av. Years of Ed. In Sample"
putexcel I1 ="Av. Hours Worked, percent of full-time"
putexcel J1 ="Av. Hours Worked"
putexcel K1 ="Av. Share Foreign in City"
putexcel L1 ="Av. Share Foreign-Born in Firm"




forvalues i=2/8 {
	sum EmpStat if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0
	putexcel B`i'=`r(N)'/7, nformat(number_sep) //div by 7 for av. over the 7 years	
	putexcel C`i'=`r(mean)' , nformat(number_d2)

	sum EmpStatPriv if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0
	putexcel D`i'=`r(mean)' , nformat(number_d2)
	
	sum u2 if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0
	putexcel E`i'=`r(mean)' , nformat(number_d2)

	sum u if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0
	putexcel F`i'=`r(mean)' , nformat(number_d2)


	sum Age if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0 & u==1
	putexcel G`i'=`r(mean)' , nformat(number_d2)

	sum Educ_Ar if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0 & u==1
	putexcel H`i'=`r(mean)' , nformat(number_d2)

	sum AvTjomf if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0 & u==1
	putexcel I`i'=`r(mean)' , nformat(number_d2)

	sum AvArbtim if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0 & u==1
	putexcel J`i'=`r(mean)' , nformat(number_d2)
	
	sum FirmForShare if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0 & u==1
	putexcel K`i'=`r(mean)' , nformat(number_d2)

	sum KomFor if AdjCont==`i'-1 & Age<65 & Age>24 & Kon==0 & u==1
	putexcel L`i'=`r(mean)' , nformat(number_d2)



	
}
